package edu.fzu.costlog.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.fzu.costlog.domain.CostCategory;
import edu.fzu.costlog.domain.CostLog;
import edu.fzu.costlog.utils.DBUtils;


public class CostCategoryDaoJDBCImpl implements CostCategoryDao {
    
	private static final String SQL_ADD=
		"insert into type_log(type_name,type_time) values(?,?)";
	private static final String SQL_LOADALL=
	    "select * from type_log order by type_id desc";
	private static final String SQL_DEL=
	    "delete from type_log where type_id=?";
	private static final String SQL_GETBYID=
	    "select * from type_log where type_id=?";
	private static final String SQL_UPDATE=
	    "update type_log set type_name=?,type_time=? where type_id=?";
	
	public void addCategory(CostCategory category) {
		
	    DBUtils utils = new DBUtils();	
	    Connection conn = null;
	    PreparedStatement pstmt = null;
		
		conn = utils.getConn();
	    
	    try {
	    	
			pstmt = conn.prepareStatement(SQL_ADD);
			
			pstmt.setString(1, category.getCategoryName());
			pstmt.setTimestamp(2, new java.sql.Timestamp(category.getCreateTime().getTime()));
			
			pstmt.executeUpdate();
		} 
	    catch (SQLException e) {
			
			e.printStackTrace();
		}
	    finally{
	    	
			utils.releaseResource(conn, pstmt, null);
		}
	}

	public List<CostCategory> loadall() {
		
		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<CostCategory> categoryList = null;
		
		conn=utils.getConn();
		
		try {
			
			pstmt = conn.prepareStatement(SQL_LOADALL);
			rset = pstmt.executeQuery();
			categoryList  = new ArrayList<CostCategory>();
			
			while(rset.next()){
				
			  CostCategory category = new CostCategory();
			  
			  category.setCategoryId(rset.getInt("type_id"));
			  category.setCategoryName(rset.getString("type_name"));
			  category.setCreateTime(rset.getTime("type_time"));
			  
			  categoryList.add(category);
			}	
		} 
		catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally{
			
			utils.releaseResource(conn, pstmt, rset);	
		}
		
		return categoryList;
	}

	public void delLog(Integer categoryId) {
		
	    DBUtils utils = new DBUtils();	
	    Connection conn = null;
	    PreparedStatement pstmt = null;
		
		conn = utils.getConn();
	    
		try {
			
			pstmt=conn.prepareStatement(SQL_DEL);
			pstmt.setInt(1, categoryId);
			pstmt.executeUpdate();
		} 
		catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally{	
			
			utils.releaseResource(conn, pstmt, null);			
		}
	}

	public CostCategory getCategoryById(Integer categoryId) {
		
		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		CostCategory category = null;
		
		conn=utils.getConn();
		
		try {
			
			pstmt = conn.prepareStatement(SQL_GETBYID);
			pstmt.setInt(1, categoryId);
			rset = pstmt.executeQuery();

			if(rset.next()){
				
			  category = new CostCategory();
			  	  
			  category.setCategoryId(rset.getInt("type_id"));
			  category.setCategoryName(rset.getString("type_name"));
			  category.setCreateTime(rset.getTime("type_time"));
			}	
		} 
		catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally{
			
			utils.releaseResource(conn, pstmt, rset);	
		}
		
		return category;
	}

	public void updateCategory(CostCategory category) {
		
	    DBUtils utils = new DBUtils();	
	    Connection conn = null;
	    PreparedStatement pstmt = null;

		conn = utils.getConn();
	    
	    try {
	    	
			pstmt = conn.prepareStatement(SQL_UPDATE);
			
			pstmt.setString(1, category.getCategoryName());
			pstmt.setTimestamp(2, new java.sql.Timestamp(category.getCreateTime().getTime()));
			pstmt.setInt(3, category.getCategoryId());
			
			pstmt.executeUpdate();
		} 
	    catch (SQLException e) {
			
			e.printStackTrace();
		}
	    finally{
	    	
			utils.releaseResource(conn, pstmt, null);
		}
	}

}
